package com.crm.dao.saling;

import com.crm.pojo.*;
import com.crm.vo.CODetailsVO;
import com.crm.vo.ContractClientCodetailsVO;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.math.BigDecimal;
import java.util.List;

/**
 * Created by dr5hx on 2018/8/9
 */
public interface CODetailsDao extends CrudRepository<CODetail, Long> {
    /**
     * 显示对应合同的订单详情
     *
     * @param contract
     * @return
     */
    @Query("select new com.crm.vo.CODetailsVO(c,p) from CODetail c,ProductionDetail p where c.cODetailsIsDel='否' and c.contract=?1  and c.cOProductId=p.pid")
    public List<CODetailsVO> showOrderDetailsList(Contract contract);

    /**
     * 删除对应订单详情
     *
     * @param contractId
     */
    @Query(nativeQuery = true, value = "update C_O_DETAILS set C_O_DETAILS_IS_DEL='是' where CONTRACT_ID=?1")
    //更新数据库需要使用注解modifying
    @Modifying
    public void deleteCODetailsById(BigDecimal contractId);

    /**
     * 查询合同细节id的对应的完整数据
     *
     * @param coId
     * @return
     */
    @Query("select new com.crm.vo.CODetailsVO(c,p) from CODetail c,ProductionDetail p where c.cODetailsIsDel='否' and c.cOId=?1  and c.cOProductId=p.pid")
    public CODetailsVO getOneCODetail(Long coId);

    /**
     * 查询合同id对应的所有合同详情的所有数据所包含的金额
     *
     * @param contract
     * @return
     */
    @Query("select  sum(codetail.cOAmount*codetail.cOPrice) from  CODetail codetail where codetail.contract=?1")
    public BigDecimal getCodetailMoneyByContractId(Contract contract);

    /**
     * 根据客户编号查询该客户得所有订单，以及每个订单对应的订单详情
     *
     * @return
     */
    @Query("select  new com.crm.vo.CODetailsVO(codetail,p) from Contract contract, CODetail codetail, ProductionDetail p where contract.fkCustomerId=?1 and codetail.contract=contract and codetail.cOProductId=p.pid ")
    public List<CODetailsVO> getAllCODetailVOByClientId(BigDecimal clientId);

    /**
     * 查询对应客户的对应的发货信息
     *
     * @param clientId
     * @return
     */

    @Query("select dispatcherDetail from DispatcherDetail dispatcherDetail,Dispatcher dispatcher,Contract contract where dispatcherDetail.dispatcher=dispatcher and dispatcher.fkOrderId=contract.contractId and contract.fkCustomerId=?1 and dispatcher.dispatcherIsDel='否' and dispatcherDetail.dispatcherDetailsIsDel='否'")


    public List<DispatcherDetail> getDispatcherDetailRecordListByClientId(BigDecimal clientId);

    /**
     * 根据客户编号查询发货单
     *
     * @return
     */
    @Query("select  dispatcher from  Dispatcher dispatcher,Contract contract where  dispatcher.fkOrderId=contract.contractId and contract.fkCustomerId=?1 and dispatcher.dispatcherIsDel='否'")
    public List<Dispatcher> getDispatcherRecordListByClientId(BigDecimal clienId);


    /**
     * 根据客户编号查询计划回款
     *
     * @param clientId
     * @return
     */
    @Query("select planingBack from PlaningBack planingBack,Contract  contract where planingBack.planingBackIsDel='否' and planingBack.fkPlaningBackOrderId=contract.contractId and contract.fkCustomerId=?1")
    public List<PlaningBack> getPlaningBackRecordListByClientId(BigDecimal clientId);

    /**
     * 根据客户编号查询回款
     *
     * @param clientId
     * @return
     */
    @Query("select back from  Back  back, PlaningBack planingBack,Contract  contract where  back.fkBackPlaningBackId=planingBack.planingBackId and planingBack.planingBackIsDel='否' and planingBack.fkPlaningBackOrderId=contract.contractId and contract.fkCustomerId=?1 and back.backIsDel='否'")
    public List<Back> getBackRecordListByClientId(BigDecimal clientId);

    /**
     * 查询订单详细细节
     *
     * @return
     */

    @Query("select new com.crm.vo.ContractClientCodetailsVO(contract,codetail,productionDetail,clientInfo,userInfo) from Contract contract,ProductionDetail  productionDetail,ClientInfo  clientInfo,Userinfo  userInfo,CODetail codetail where contract.fkCustomerId=clientInfo.cid and contract.contractEmpno=userInfo.uId and codetail.contract=contract and codetail.cOProductId=productionDetail.pid and contract.contractIsDel='否'")
    public List<ContractClientCodetailsVO> getContractClientCodetailsVOList(Pageable pageable);

}
